import datetime
try:
    import pysqlite2.dbapi2 as sqlite3
except ImportError:
    import sqlite3 

import banks


class DbError(Exception):
    pass


class CurrenciesDb(object):
    def __init__(self, db_name):
        try:
            self.conn = sqlite3.connect(db_name)
        except sqlite3.OperationalError:
            raise DbError('Cannot open `%s`' % db_name)
        self._create()

    def _create(self):
        c = self.conn.cursor()
        try:
            c.execute('select 1 from rates')
        except sqlite3.OperationalError:
            c.execute('''
                CREATE TABLE rates (
                    source_quantity float,
                    source_currency int,
                    target_quantity float,
                    target_currency int,
                    date datetime,
                    bank int
                )''')
            self.conn.commit()
            
    def get_rate(self, source_currency, target_currency, date, bank):
        """
        Return k, where:
        source_currency_money * k = target_currency_money
        k = target_currency_money / source_currency_money
        """
        for currency1, currency2, is_opposite, force_load in (
            (source_currency, target_currency, False, False),
            (target_currency, source_currency, True,  False),
            (source_currency, target_currency, False, True),
            (target_currency, source_currency, True,  False),
        ): 
            if force_load:
                if self._num_entries({
                    'date': date.strftime('%Y-%m-%d'),
                    'bank': bank,
                }) > 0: return None
                data = banks.loader(bank)(date)
                self.insert_data(data, date, bank)

            res = self._fetch_one({
                'source_currency': currency1,
                'target_currency': currency2,
                'date': date.strftime('%Y-%m-%d'),
                'bank': bank,
            })

            if res:
                source_currency_money, target_currency_money = res
                if is_opposite:
                    return float(source_currency_money) / target_currency_money
                else:
                    return float(target_currency_money) / source_currency_money
                        
        return None
        #raise DbError, 'Rate for %d <-> %d not found' % (source_currency, target_currency)
            

    def _fetch_one(self, cond):
        c = self.conn.cursor()
        sql = '''
            SELECT
            source_quantity, target_quantity  
            FROM rates WHERE %s
            ''' % ' AND '.join('%s = ? ' % k for k in cond)
        c.execute(sql, tuple(cond.values()))
        rows = c.fetchall()
        
        if rows:
            if len(rows) > 1:
                raise DbError, 'To many results retured'
            return rows[0]

        return None            
            
    def _num_entries(self, cond):
        c = self.conn.cursor()
        sql = '''
            SELECT COUNT(*)  
            FROM rates WHERE %s
            ''' % ' AND '.join('%s = ? ' % k for k in cond)
        c.execute(sql, tuple(cond.values()))
        return int(c.fetchall()[0][0])
        
    def insert_data(self, data, date, bank):
        c = self.conn.cursor()        
        for sq, sc, tq, tc in data:
            c.execute('''
                INSERT INTO rates
                (source_quantity, source_currency,
                target_quantity, target_currency,
                date, bank)
                VALUES
                (?, ?,
                ?, ?,
                ?, ?)''',
                (sq, sc, 
                tq, tc,
                date.strftime('%Y-%m-%d'), bank))
        self.conn.commit()        
